package org.transfer.utils.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.transfer.utils.ConfigUtil;

public final class JDBCMYUtils {
	private static String driverMysql = "com.mysql.jdbc.Driver";
	private static String hostMysql = ConfigUtil.getConfigure().getProperty("mySqlHostname");
	private static String portMysql = ConfigUtil.getConfigure().getProperty("mySqlDBport");
	private static String userMysql = ConfigUtil.getConfigure().getProperty("mySqlUsername");
	private static String passwordMysql = ConfigUtil.getConfigure().getProperty("mySqlPassword");

	private static String driverSqlserver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static String hostSqlserver = ConfigUtil.getConfigure().getProperty("hostname");
	private static String portSqlserver = ConfigUtil.getConfigure().getProperty("DBPort");
	private static String userSqlserver = ConfigUtil.getConfigure().getProperty("username");
	private static String passwordSqlserver = ConfigUtil.getConfigure().getProperty("password");

	private JDBCMYUtils() {
	}

	static {
		/**
		 * 驱动注册
		 */
		try {
			Class.forName(driverMysql);
			Class.forName(driverSqlserver);
		} catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError(e);
		}

	}

	/**
	 * 获取 Connetion
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnectionMy(String dbName) throws SQLException {
		String url = "jdbc:mysql://" + hostMysql + ":" + portMysql + "/" + dbName
				+ "?useUnicode=true&characterEncoding=UTF-8";
		return DriverManager.getConnection(url, userMysql, passwordMysql);
	}

	/**
	 * 获取 Connetion
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnectionMs(String dbName) throws SQLException {
		String url = "jdbc:sqlserver://" + hostSqlserver + ":" + portSqlserver + ";DatabaseName=" + dbName;
		return DriverManager.getConnection(url, userSqlserver, passwordSqlserver);
	}

	/**
	 * 释放资源
	 * 
	 * @param conn
	 * @param st
	 * @param rs
	 */
	public static void closeResource(Connection conn, Statement st, ResultSet rs) {
		closeResultSet(rs);
		closeStatement(st);
		closeConnection(conn);
	}

	/**
	 * 释放连接 Connection
	 * 
	 * @param conn
	 */
	public static void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		// 等待垃圾回收
		conn = null;
	}

	/**
	 * 释放语句执行者 Statement
	 * 
	 * @param st
	 */
	public static void closeStatement(Statement st) {
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		// 等待垃圾回收
		st = null;
	}

	/**
	 * 释放结果集 ResultSet
	 * 
	 * @param rs
	 */
	public static void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		// 等待垃圾回收
		rs = null;
	}

	/**
	 * 执行更新
	 *
	 * @param sql
	 *            传入的预设的sql语句
	 * @param params
	 *            问号参数列表
	 * @return 影响行数
	 */
	public static int execUpdate(PreparedStatement pstmt, Object[] params) {
		try {
			if (params != null) {
				// 设置参数列表
				for (int i = 0; i < params.length; i++) {
					// 因为问号参数的索引是从1开始，所以是i+1，将所有值都转为字符串形式，好让setObject成功运行
					pstmt.setObject(i + 1, params[i] + "");
				}
			}

			return pstmt.executeUpdate();// 执行更新，并返回影响行数

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeStatement(pstmt);
		}
		return 0;
	}

	/**
	 * 执行查询
	 *
	 * @param sql
	 *            传入的预设的sql语句
	 * @param params
	 *            问号参数列表
	 * @return 查询后的结果
	 */
	public static List<Map<String, Object>> execQuery(PreparedStatement pstmt, Object[] params) {
		try {
			if (params != null) {
				// 设置参数列表
				for (int i = 0; i < params.length; i++) {
					// 因为问号参数的索引是从1开始，所以是i+1，将所有值都转为字符串形式，好让setObject成功运行
					pstmt.setObject(i + 1, params[i] + "");
				}
			}

			// 执行查询
			ResultSet rs = pstmt.executeQuery();

			List<Map<String, Object>> al = new ArrayList<Map<String, Object>>();

			// 获得结果集元数据（元数据就是描述数据的数据，比如把表的列类型列名等作为数据）
			ResultSetMetaData rsmd = rs.getMetaData();

			// 获得列的总数
			int columnCount = rsmd.getColumnCount();

			// 遍历结果集
			while (rs.next()) {
				Map<String, Object> hm = new HashMap<String, Object>();
				for (int i = 0; i < columnCount; i++) {
					// 根据列索引取得每一列的列名,索引从1开始
					String columnName = rsmd.getColumnLabel(i + 1);
					// 根据列名获得列值
					Object columnValue = rs.getObject(columnName);
					// 将列名作为key，列值作为值，放入hm中，每个hm相当于一条记录
					hm.put(columnName, columnValue);
				}
				// 将每个hm添加到al中,al相当于是整个表，每个hm是里面的一条记录
				al.add(hm);
			}
			closeResultSet(rs);

			return al;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeStatement(pstmt);
		}

		return null;
	}
}
